import pandas as pd

def add_website_columns(input_file, output_file):
    """
    Add new columns 'CB网站' and 'PC网站' to the Excel file with URLs based on 
    the 'CB号' and 'CID号' columns respectively.
    
    Parameters:
    input_file (str): Path to the input Excel file
    output_file (str): Path to save the modified Excel file
    """
    # Read the Excel file, forcing the CID号 column to be treated as string
    df = pd.read_excel(input_file, dtype={'CID号': str})
    
    # Create the 'CB网站' column by constructing the URL
    df['CB网站'] = 'https://www.chemicalbook.com/ProductChemicalProperties' + df['CB号'] + '.htm'
    
    # Create the 'PC网站' column by constructing the URL
    # Only create URLs for rows with valid CID numbers
    df['PC网站'] = df['CID号'].apply(
        lambda cid: f'https://pubchem.ncbi.nlm.nih.gov/compound/{cid}' if pd.notna(cid) and cid != "" else ""
    )
    
    # Save the modified DataFrame to a new Excel file
    df.to_excel(output_file, index=False)
    
    print(f"Modified file saved to {output_file}")

# Example usage
input_file = './API/merged_result_new.xlsx'
output_file = './API/merged_result_new.xlsx'
add_website_columns(input_file, output_file)